package db.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.db.Table;

import db.beans.M_CategoryBean;

public class M_CategoryTable extends Table {

	public static final String TABLE_NAME = "M_CATEGORY";
	public static final String COL_CATEGORY_CODE = "CATEGORY_CODE";
	public static final String COL_CATEGORY_NAME = "CATEGORY_NAME";
	public static final String COL_BELONG_CATEGORY_CODE = "BELONG_CATEGORY_CODE ";
	public static final String COL_DELETE_FLAG = "DELETE_FLAG";

	public static M_CategoryBean[] selectAll(Connection conn) throws SQLException {
		final String sql = "SELECT " 
				+ COL_CATEGORY_CODE + ","
				+ COL_CATEGORY_NAME + "," 
				+ COL_BELONG_CATEGORY_CODE + ","
				+ COL_DELETE_FLAG 
				+ " FROM " + TABLE_NAME;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Vector<M_CategoryBean> v = new Vector<M_CategoryBean>();
		try {
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			int i;
			while (rs.next()) {
				i = 0;
				M_CategoryBean rec = new M_CategoryBean();
				rec.setCategoryCode(rs.getInt(++i));
				rec.setCategoryName(rs.getString(++i));
				rec.setBelongCategoryCode(rs.getInt(++i));
				rec.setDeleteFlag(rs.getInt(++i) == NOT_DELETE_FLAG);
				v.add(rec);
			}
		} finally {
			if (rs != null) {
				rs.close();
			}

			if (stmt != null) {
				stmt.close();
			}
		}
		return (M_CategoryBean[]) v.toArray(new M_CategoryBean[v.size()]);
	}
	
	public static M_CategoryBean[] selectCategoryBySrcWebsiteName(Connection conn, String websiteName) throws SQLException {
		final String sql = "SELECT " 
				+ COL_CATEGORY_CODE + ","
				+ COL_CATEGORY_NAME + "," 
				+ COL_BELONG_CATEGORY_CODE + ","
				+ COL_DELETE_FLAG 
				+ " FROM " + TABLE_NAME
				+ "," + M_WebsiteTable.TABLE_NAME
				+ "," + M_WebsiteCategoryTable.TABLE_NAME
				+ "," + M_CategoryMappingTable.TABLE_NAME
				+ " WHERE "
				+ M_WebsiteTable.COL_WEBSITE_NAME + "=?"
				+ " AND "
				+ M_WebsiteTable.COL_WEBSITE_CODE + "=" +M_WebsiteCategoryTable.COL_WEBSITE_CODE
				+ " AND "
				+ M_WebsiteCategoryTable.COL_WEBSITE_CATEGORY_CODE + "=" +M_CategoryMappingTable.COL_WEBSITE_CATEGORY_CODE
				+ " AND "
				+ COL_CATEGORY_CODE + "=" + M_CategoryMappingTable.COL_CATEGORY_CODE;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Vector<M_CategoryBean> v = new Vector<M_CategoryBean>();
		try {
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			int i;
			while (rs.next()) {
				i = 0;
				M_CategoryBean rec = new M_CategoryBean();
				rec.setCategoryCode(rs.getInt(++i));
				rec.setCategoryName(rs.getString(++i));
				rec.setBelongCategoryCode(rs.getInt(++i));
				rec.setDeleteFlag(rs.getInt(++i) == NOT_DELETE_FLAG);
				v.add(rec);
			}
		} finally {
			if (rs != null) {
				rs.close();
			}

			if (stmt != null) {
				stmt.close();
			}
		}
		return (M_CategoryBean[]) v.toArray(new M_CategoryBean[v.size()]);
	}
}
